/**
 * 
 */
package com.sino.directbank.service;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidPooledConnection;

/**
 * @author matianq
 *
 */
@Service
public class DirectBankService {

	@Autowired
	private DruidDataSource dataSource;

	/**
	 * 查询全国情况
	 * @return
	 */
	public List<Map<String, Object>> getChinaMap() {
		StringBuilder sb = new StringBuilder();
		sb.append("select province,count(1) as jyl from td_data");
		sb.append(" where trunc(ts)=trunc(sysdate) ");
		sb.append(" and sub_trans_type ='直销银行'");
		sb.append(" and province is not null ");
		sb.append(" and ret_code='True' ");
		sb.append(" group by province ");
		String sql = sb.toString();
		List<Map<String, Object>> rs = execSql(sql);
		return rs;
	}
	
	/**
	 * 查询全国销量top10
	 * @return
	 */
	public List<Map<String, Object>> getChinaTopTen() {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT PROVINCE,JYL,rownum FROM ");
		sb.append("(select province,count(1) as jyl from td_data");
		sb.append(" where trunc(ts)=trunc(sysdate) ");
		sb.append(" and sub_trans_type ='直销银行' ");
		sb.append(" and province is not null ");
		sb.append(" and ret_code='True'");
		sb.append(" group by province ORDER BY JYL DESC) WHERE ROWNUM<=10 ");
		String sql = sb.toString();
		List<Map<String, Object>> rs = execSql(sql);
		return rs;
	}
	
	/**
	 * 查询全国实时交易量
	 * @return
	 */
	public List<Map<String, Object>> getChinaRealTime() {
		StringBuilder sb = new StringBuilder();
		sb.append("select HH,COUNT(1) AS JYL FROM  ");
		sb.append("( select   to_char(ts, 'hh24')||':'|| decode( floor(to_char(ts, 'mi') / 5) * 5,0,'00',5,'05',floor(to_char(ts, 'mi') / 5) * 5)  as HH from td_data t ");
		sb.append(" where trunc(ts)=trunc(sysdate) ");
		sb.append(" and sub_trans_type ='直销银行'");
		sb.append(" and clientip is not null ");
		sb.append(" and province is not null ");
		sb.append(" and ret_code='True' )");
		sb.append(" GROUP BY HH ORDER BY HH ");
		String sql = sb.toString();
		List<Map<String, Object>> rs = execSql(sql);
		return rs;
	}
	
	/**
	 * 查询全国交易种类top10
	 * @return
	 */
	public List<Map<String, Object>> ChinaTypeTopTen() {
		String sql = "SELECT * from(SELECT COUNT(1) as nm, EXTEND01  FROM TD_DATA WHERE SUB_TRANS_TYPE='直销银行' "
		+"and EXTEND01 is not null and trunc(ts)=trunc(sysdate) and ret_code='True' GROUP BY EXTEND01 ORDER BY nm DESC) where ROWNUM<=10";
		List<Map<String, Object>> rs = execSql(sql);
		return rs;
	}
	
	/**
	 * 查询全国交易总量
	 * @return
	 */
	public List<Map<String, Object>> ChinaSaleAmount() {
		String sql = "SELECT count(1) jshu,round(sum(payamt)/10000,2) as sumpayamt from TD_DATA where SUB_TRANS_TYPE='直销银行' "
				+ "and RET_CODE='True' and trunc(ts)=trunc(sysdate)";
		List<Map<String, Object>> rs = execSql(sql);
		return rs;
	}
	
	
	/**
	 * runSql
	 * @param sql
	 * @return
	 */
	public List<Map<String, Object>> execSql(String sql) {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			DruidPooledConnection conn = dataSource.getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			ResultSetMetaData md = rs.getMetaData();
			// 得到结果集(rs)的结构信息，比如字段数、字段名等
			int columnCount = md.getColumnCount(); // 返回此 ResultSet 对象中的列数
			Map<String, Object> rowData = new HashMap<String, Object>();
			while (rs.next()) {
				rowData = new HashMap<String, Object>(columnCount);
				for (int i = 1; i <= columnCount; i++) {
					rowData.put(md.getColumnName(i), rs.getObject(i));
				}
				list.add(rowData);
			}
			rs.close();
			stmt.close();
			conn.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
   
		return list;
	}
}
